package com.huike.stock_analysis.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.huike.stock_analysis.entity.CapitalInfo;
import com.huike.stock_analysis.entity.Rank;


/**
 * 
 * @author mzLEI
 * @date 2020年7月2日
 */
/*
 * 资金统计
 */
@Repository
public class CapitalDao {
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 
	 * @param code
	 * @return
	 * 资金信息查询
	 */
	public List<CapitalInfo> queryCapital(String code){
		String sql = "SELECT * FROM `ads_zjtj1` WHERE `ads_id` = " + code + " ORDER BY `ads_date` DESC LIMIT 30";
		List<CapitalInfo> capitalFlows = jdbcTemplate.query(sql,  new RowMapper<CapitalInfo>() {
			
			@Override
			public CapitalInfo mapRow(ResultSet rs, int col) throws SQLException {
				CapitalInfo capitalFlow = new CapitalInfo();
				capitalFlow.setNetFlow(rs.getDouble("ads_zjjlr"));
				capitalFlow.setMainNetFlow(rs.getDouble("ads_zlzjjlr"));
				capitalFlow.setNetFlowOfSame(rs.getDouble("ads_hypjzjjlr"));
				capitalFlow.setMainNetFlowOfSame(rs.getDouble("ads_hypjzlzjjlr"));
				capitalFlow.setDate(rs.getDate("ads_date"));
				capitalFlow.setMainFlowRatio(rs.getDouble("ads_zlzjjlrzb"));
				capitalFlow.setSmall(rs.getDouble("ads_xdjlrje"));
				capitalFlow.setMid(rs.getDouble("ads_zdjlrje"));
				capitalFlow.setBig(rs.getDouble("ads_ddjlrje"));
				capitalFlow.setSuperBig(rs.getDouble("ads_cddjlrje"));
				capitalFlow.setChange(rs.getDouble("ads_zdf"));
				return capitalFlow;
			}
		});
		
		return capitalFlows;
	}
	
	
	/**
	 * 
	 * @param code
	 * @return
	 *  查询股票所属的板块
	 */
	public HashMap<String, String> bkName(String code){
		String sql = "SELECT `name`, `ads_date` FROM `ads_zjtj1` WHERE `ads_id` = " + code + " ORDER BY `ads_date` DESC LIMIT 1";
		HashMap<String, String> name = jdbcTemplate.queryForObject(sql,  new RowMapper<HashMap<String, String>>() {
			
			@Override
			public HashMap<String, String> mapRow(ResultSet rs, int col) throws SQLException {
				HashMap<String, String> map = new HashMap<>();
				map.put("bkname", rs.getString("name"));
				map.put("date", rs.getDate("ads_date").toString());
				return map;
			}
		});
		
		return name;
	}
	
	/**
	 * 
	 * @param code
	 * @return
	 *  前十名 
	 */
	public List<Rank> queryTop10(String code){
		Map<String, String> bk = bkName(code);
		
		String sql = "SELECT `ads_name`, `ads_zlzjjlr` FROM `ads_zjtj1` WHERE `name` = '" + bk.get("bkname") + "' and `ads_date` = '" +
					bk.get("date") + "' ORDER BY `ads_zlzjjlr` DESC LIMIT 10";
		//System.out.println(sql);
		List<Rank> ranks = jdbcTemplate.query(sql,  new RowMapper<Rank>() {
			
			@Override
			public Rank mapRow(ResultSet rs, int col) throws SQLException {
				Rank rank = new Rank();
				rank.setName(rs.getString("ads_name"));
				rank.setMoney(rs.getDouble("ads_zlzjjlr"));
				return rank;
			}
		});
		
		return ranks;
	}
	/**
	 * 
	 * @param code
	 * @return
	 *  后十名
	 */
	public List<Rank> queryBottom10(String code){
		Map<String, String> bk = bkName(code);
		
		String sql = "SELECT `ads_name`, `ads_zlzjjlr` FROM `ads_zjtj1` WHERE `name` = '" + bk.get("bkname") + "' and `ads_date` = '" +
					bk.get("date") + "' ORDER BY `ads_zlzjjlr` LIMIT 10";
		//System.out.println(sql);
		List<Rank> ranks = jdbcTemplate.query(sql,  new RowMapper<Rank>() {
			
			@Override
			public Rank mapRow(ResultSet rs, int col) throws SQLException {
				Rank rank = new Rank();
				rank.setName(rs.getString("ads_name"));
				rank.setMoney(rs.getDouble("ads_zlzjjlr"));
				return rank;
			}
		});
		
		return ranks;
	}
	
	/**
	 * 
	 * @param code
	 * @return stock
	 * 股票行业涨跌排行和市场排行
	 */
	
	public String rank(String code){
		Map<String, String> bkname = bkName(code);
		String bk = "'"+bkname.get("bkname")+"'";
		System.out.print(bkname.get("bkname")+bkname.get("date"));
		//查询市场所有股票数量的sql
		String sqlForTotalNums = "select count(*) from(SELECT distinct `ads_id` FROM `ads_zjtj1`) as a";
		//查询同行所有股票数量的sql
		String sqlForHYNums = "select count(*) from(SELECT distinct `ads_id` FROM `ads_zjtj1` where `name` = " + bk + " ) as a";
		//查询股票最新涨跌幅
		String sqlForChange = "select `ads_zdf` from `ads_zjtj1` where `ads_id` = " + code +" and `ads_date` = '" + bkname.get("date")+"'";
		
		Integer totalNums = jdbcTemplate.queryForObject(sqlForTotalNums, Integer.class);
		Integer hYNums = jdbcTemplate.queryForObject(sqlForHYNums, Integer.class);
		Double change = jdbcTemplate.queryForObject(sqlForChange, Double.class);
		
		//查询行业排名sql
		String sqlForHy = "select count(*) from(select `ads_zdf` from `ads_zjtj1` where `ads_zdf` > " + change+
				" and `name` = " + bk + " and `ads_date` = '" + bkname.get("date") + "' ) as a";
		//查询总排名
		String sqlForTotal = "select count(*) from(select `ads_zdf` from `ads_zjtj1` where `ads_zdf` > " + change + " and `ads_date` = '" + bkname.get("date")+ "' ) as a";
		Integer hYRank = jdbcTemplate.queryForObject(sqlForHy, Integer.class) + 1;
		Integer totalRank = jdbcTemplate.queryForObject(sqlForTotal, Integer.class) + 1;
		return hYRank+","+hYNums+","+totalRank+","+totalNums;
	
	}
	
	
}
